const db = require("../../db");

/**
 * POST 单位的回调函数
 * @param unitName  单位名称
 * @param unitcode  单位代码
 * @param unitType  类型
 * @param remark  备注
 */
exports.add = (req, res) => {
  // 接收表单的数据
  const unitInfo = req.body;
  // 定义 SQL 语句
  let sql = `
  INSERT INTO account_out_bound 
  (unitName, unitId, dateTime, createdTime, sortName, sortId, number, totalAmount, totalAmountChinese, manager, warehouse,warehouseId, accountName, handledName, role, userName, userId, remark)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
  let values = [
    unitInfo.unitName,
    unitInfo.unitId,
    unitInfo.dateTime,
    Date.now(), // 例如 createdTime 使用 Date.now() 来获取当前时间戳
    unitInfo.sortName,
    unitInfo.sortId,
    unitInfo.number,
    unitInfo.totalAmount,
    unitInfo.totalAmountChinese,
    unitInfo.manager,
    unitInfo.warehouse,
    unitInfo.warehouseId,
    unitInfo.accountName,
    unitInfo.handledName,
    unitInfo.role,
    unitInfo.userName,
    unitInfo.userId,
    unitInfo.remark,
  ];
  // 调用 db.query() 执行 sql 语句
  db.query(sql, values, (error, result) => {
    if (error) return res.cc(error);
    // 判断影响行数是否为 1
    if (result.affectedRows !== 1) return res.cc("新增出库单失败！");
    const insertId = result.insertId;
    if (!unitInfo.subTable || !Array.isArray(unitInfo.subTable)) {
      return res.cc("未提供有效的从表数据！");
    }
    const subTableValues = unitInfo.subTable.map((item) => ({
      pid: insertId,
      outBoundId: item.outBoundId,
      outBoundName: item.outBoundName,
      outBoundType: item.outBoundType,
      outBoundUnit: item.outBoundUnit,
      outBoundUnitName: item.outBoundUnitName,
      outBoundNum: item.outBoundNum,
      outBoundPrice: item.outBoundPrice,
      outBoundMoney: item.outBoundMoney,
      remark: unitInfo.remark,
      warehouseId: unitInfo.warehouseId,
      warehouse: unitInfo.warehouse,
      role: unitInfo.role,
      userName: unitInfo.userName,
      userId: unitInfo.userId,
      storeNum: item.inventoryNum,
    }));
    // 定义 SQL 语句插入从表
    let sql2 =
      "insert into account_out_bound_detail (pid,outBoundId, outBoundName, outBoundType, outBoundUnit, outBoundUnitName, outBoundNum, outBoundPrice, outBoundMoney, remark, warehouseId, warehouse, role, userName, userId, storeNum) values ?";
    db.query(
      sql2,
      [subTableValues.map((subItem) => Object.values(subItem))],
      (error, result) => {
        if (error) return res.cc(error);
        // 更新入库表的库存数量
        unitInfo.subTable.map((_item) => {
          let sql3 =
            "UPDATE account_in_bound_detail SET storeNum = ? WHERE warehouseId = ? AND id = ?";
          // Assuming there is only one item in the subTable (item.outBoundId is used)
          db.query(
            sql3,
            [_item.inventoryNum, unitInfo.warehouseId, _item.outBoundId],
            (error3, result3) => {
              if (error3) {
                return res.cc(error3);
              }
              if (result3.affectedRows !== 1) {
                return res.cc("更新入库表失败！");
              }
            }
          );
        });
        return res.cc("新增出库单成功", true);
      }
    );
  });
};

exports.update = (req, res) => {
  const unitInfo = req.body;

  db.beginTransaction((error) => {
    if (error) {
      console.error("Transaction Begin Error:", error);
      return res.cc("Transaction begin failed.");
    }

    const updateMainTableSQL = `
      UPDATE account_out_bound 
      SET
        unitName = ?,
        unitId = ?,
        dateTime = ?,
        sortName = ?,
        sortId = ?,
        number = ?,
        totalAmount = ?,
        totalAmountChinese = ?,
        manager = ?,
        warehouse = ?,
        warehouseId = ?,
        accountName = ?,
        handledName = ?,
        userName = ?,
        userId = ?,
        remark = ?
      WHERE id = ?
    `;

    const mainTableValues = [
      unitInfo.unitName,
      unitInfo.unitId,
      unitInfo.dateTime,
      unitInfo.sortName,
      unitInfo.sortId,
      unitInfo.number,
      unitInfo.totalAmount,
      unitInfo.totalAmountChinese,
      unitInfo.manager,
      unitInfo.warehouse,
      unitInfo.warehouseId,
      unitInfo.accountName,
      unitInfo.handledName,
      unitInfo.userName,
      unitInfo.userId,
      unitInfo.remark,
      unitInfo.id,
    ];

    db.query(updateMainTableSQL, mainTableValues, (error, mainTableResult) => {
      if (error) {
        console.error("Main Table Update Error:", error);
        return db.rollback(() => res.cc("Main table update failed."));
      }

      if (mainTableResult.affectedRows !== 1) {
        console.error("Main Table Update Warning:", mainTableResult);
        return db.rollback(() => res.cc("Main table not updated as expected."));
      }

      const deleteDetailsTableSQL = `
        DELETE FROM account_out_bound_detail
        WHERE pid = ?
      `;

      db.query(deleteDetailsTableSQL, [unitInfo.id], (error, deleteResult) => {
        if (error) {
          console.error("Delete Details Table Error:", error);
          return db.rollback(() =>
            res.cc("Failed to delete old details entries.")
          );
        }

        const insertDetailsTableSQL = `
          INSERT INTO account_out_bound_detail (
           outBoundId, outBoundName, outBoundType, outBoundUnit, outBoundUnitName,
            outBoundNum, outBoundPrice, outBoundMoney, storeNum, remark,
            warehouseId, warehouse, role, userName, userId, pid
          ) VALUES ?
        `;

        const subTableValues = unitInfo.subTable.map((item) => [
          item.outBoundId,
          item.outBoundName,
          item.outBoundType,
          item.outBoundUnit,
          item.outBoundUnitName,
          item.outBoundNum,
          item.outBoundPrice,
          item.outBoundMoney,
          item.storeNum,
          unitInfo.remark,
          unitInfo.warehouseId,
          unitInfo.warehouse,
          unitInfo.role,
          unitInfo.userName,
          unitInfo.userId,
          unitInfo.id,
        ]);

        console.log("this1----", subTableValues);
        db.query(
          insertDetailsTableSQL,
          [subTableValues],
          (error, detailsTableResult) => {
            if (error) {
              console.error("Insert Details Table Error:", error);
              return db.rollback(() => res.cc("Details table insert failed."));
            }

            db.commit((error) => {
              if (error) {
                console.error("Transaction Commit Error:", error);
                return res.cc("Transaction commit failed.");
              }

              return res.cc("更新出库单成功!", true);
            });
          }
        );
      });
    });
  });
};

exports.del = (req, res) => {
  // 接收表单的数据
  const boundInfo = req.body;
  let sql = "select * from account_out_bound where id=?";
  db.query(sql, [boundInfo.id], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该入库单记录!");
    }
    // 定义删除子表记录的 SQL 语句
    const deleteDetailSql = "DELETE FROM account_out_bound_detail WHERE pid=?";
    // 定义删除主表记录的 SQL 语句
    const deleteMainSql = "DELETE FROM account_out_bound WHERE id=?";
    db.query(deleteDetailSql, [boundInfo.id], (error, result) => {
      if (error) return res.cc(error);

      db.query(deleteMainSql, [boundInfo.id], (error, result) => {
        if (error) return res.cc(error);

        if (result.affectedRows !== 1) return res.cc("删除入库单失败！");

        return res.cc("删除入库单成功", true);
      });
    });
  });
};

exports.allList = (req, res) => {
  const page = req.body.page ? parseInt(req.body.page) : 1;
  const limit = req.body.limit ? parseInt(req.body.limit) : 10;
  const {
    unitName,
    sortName = "",
    totalAmount = "",
    remark,
    createdTime = [],
  } = req.body;
  const userInfo = req.headers["userinfo"];
  const decodedUserInfo = decodeURIComponent(userInfo);
  const userInfoObject = JSON.parse(decodedUserInfo);
  const { role, userId } = userInfoObject;
  const sqlCount =
    role === "1"
      ? "SELECT COUNT(*) AS total FROM account_out_bound"
      : "SELECT COUNT(*) AS total FROM account_out_bound WHERE userId = ?";
  const countParams = role === "1" ? [] : [userId];

  console.log("this1----createdTime", createdTime);
  db.query(sqlCount, countParams, (error, countResults) => {
    if (error) {
      return res.cc(error);
    }

    const total = countResults[0].total;
    const offset = (page - 1) * limit;
    let sql;
    let params;
    if (role === "1") {
      // If the role is 1, query all records
      sql = `
      SELECT * 
      FROM account_out_bound 
      WHERE 
        (unitName LIKE ? OR ? IS NULL) 
        AND (sortName LIKE ? OR ? IS NULL) 
        AND (totalAmount LIKE ? OR ? IS NULL)
        AND (remark LIKE ? OR ? IS NULL)
        AND (createdTime >= ? AND createdTime <= ? OR ? IS NULL)
      ORDER BY createdTime DESC 
      LIMIT ? OFFSET ?
    `;
      params = [
        `%${unitName}%`,
        unitName,
        `%${sortName}%`,
        sortName,
        `%${totalAmount}%`,
        totalAmount,
        `%${remark}%`,
        remark,
        createdTime[0],
        createdTime[1],
        createdTime[0],
        limit,
        offset,
      ];
    } else {
      sql = `
      SELECT * 
      FROM account_out_bound 
      WHERE 
        userId = ?
        AND (unitName LIKE ? OR ? IS NULL) 
        AND (sortName LIKE ? OR ? IS NULL) 
        AND (totalAmount LIKE ? OR ? IS NULL) 
        AND (remark LIKE ? OR ? IS NULL)
        AND (createdTime >= ? AND createdTime <= ? OR ? IS NULL)
      ORDER BY createdTime DESC 
      LIMIT ? OFFSET ?
    `;
      params = [
        userId,
        `%${unitName}%`,
        unitName,
        `%${sortName}%`,
        sortName,
        `%${totalAmount}%`,
        totalAmount,
        `%${remark}%`,
        remark,
        createdTime[0],
        createdTime[1],
        createdTime[0],
        limit,
        offset,
      ];
    }

    db.query(sql, params, (error, results) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc("获取单据成功", true, results, {
        total,
        page,
        limit,
      });
    });
  });
};

exports.detail = (req, res) => {
  const boundInfo = req.body;
  // 查询主表数据
  let sql = "SELECT * FROM account_out_bound WHERE id=?";
  db.query(sql, [boundInfo.id], (error, mainResult) => {
    if (error) {
      return res.cc(error);
    }
    if (mainResult.length === 0) {
      return res.cc("未找到该入库单记录!");
    }

    // 查询子表数据
    let subSql = "SELECT * FROM account_out_bound_detail WHERE pid=?";
    db.query(subSql, [boundInfo.id], (error, subResult) => {
      if (error) {
        return res.cc(error);
      }

      const mainData = mainResult[0];
      mainData.subTable = subResult; // 将子表数据整合到主表数据中

      return res.cc("获取入库单详情成功", true, mainData);
    });
  });
};

exports.getStoreNum = (req, res) => {
  const boundInfo = req.body;
  // 查询入库表
  const sqlInBound = `SELECT * FROM account_in_bound_detail where warehouseId = ? and inBoundName = ? and inBoundType = ?`;
  db.query(
    sqlInBound,
    [boundInfo.warehouseId, boundInfo.boundName, boundInfo.boundType],
    (errorIn, resultsIn) => {
      if (errorIn) {
        return res.cc(errorIn);
      }
      // 存储入库表结果的对象
      const resultMapIn = {};
      let resultArrayIn = [];
      // 遍历入库表进行求和操作
      resultsIn.forEach((item) => {
        const key = item.inBoundName + "_" + item.inBoundType;
        if (resultMapIn[key]) {
          resultMapIn[key].inBoundNum += item.inBoundNum;
        } else {
          resultMapIn[key] = { ...item };
        }
        resultArrayIn = Object.values(resultMapIn);
      });
      const sqlOutBound = `SELECT * FROM account_out_bound_detail where warehouseId = ? and outBoundName = ? and outBoundType = ?`;
      db.query(
        sqlOutBound,
        [boundInfo.warehouseId, boundInfo.boundName, boundInfo.boundType],
        (errorOut, resultsOut) => {
          if (errorOut) {
            return res.cc(errorOut);
          }
          // 存储入库表结果的对象
          const resultMapOut = {};
          let resultArrayOut = [];
          // 遍历入库表进行求和操作
          resultsOut.forEach((item) => {
            const key = item.outBoundName + "_" + item.outBoundType;
            if (resultMapOut[key]) {
              resultMapOut[key].outBoundNum += item.outBoundNum;
            } else {
              resultMapOut[key] = { ...item };
            }
            resultArrayOut = Object.values(resultMapOut);
          });

          // 将 resultArrayOut 转换为一个 Map，便于根据 key 快速查找数据
          const outMap = new Map();
          resultArrayOut.forEach((item) => {
            const key = item.outBoundName + "_" + item.outBoundType;
            if (outMap.has(key)) {
              const currentVal = outMap.get(key);
              outMap.set(key, currentVal + item.outBoundNum);
            } else {
              outMap.set(key, item.outBoundNum);
            }
          });

          // 计算 inBoundNum 减去 outBoundNum 的差值
          const diffArray = resultArrayIn.map((item) => {
            const key = item.inBoundName + "_" + item.inBoundType;
            const outBoundNum = outMap.get(key) || 0;
            return {
              ...item,
              storeNum: item.inBoundNum - outBoundNum,
            };
          });
          console.log("diffArray", diffArray);
          return res.cc("获取物品列表成功", true, diffArray);
        }
      );
    }
  );
};

// 获取当前年月，格式为 YYYY-MM
function getCurrentYearMonth(dateTime) {
  // const today = new Date();
  // const year = today.getFullYear();
  // const month = (today.getMonth() + 1).toString().padStart(2, "0");
  const time = new Date(dateTime);
  const year = time.getFullYear();
  const month = time.getMonth() + 1;
  return `${year}-${month}`;
}

// 将数字填充到指定位数，不足的位数用0填充
function padNumber(number, width) {
  return number.toString().padStart(width, "0");
}

exports.getMaxOutNumber = (req, res) => {
  const warehouseId = req.body.warehouseId;
  const dateTime = req.body.dateTime;
  // 查询最新的出库单编号
  const sql = `
    SELECT MAX(number) AS latestInBoundNo
    FROM account_out_bound
    WHERE warehouseId = ? AND YEAR(FROM_UNIXTIME(dateTime/1000)) = YEAR(FROM_UNIXTIME(?/1000)) AND MONTH(FROM_UNIXTIME(dateTime/1000)) = MONTH(FROM_UNIXTIME(?/1000));
  `;
  db.query(sql, [warehouseId, dateTime, dateTime], (error, results) => {
    if (error) {
      return res.cc(error);
    }
    // 获取最新的出库单编号
    console.log(1, results);
    const latestInBoundNo = results[0].latestInBoundNo;
    // 生成新的编号
    let newInBoundNo;
    if (!latestInBoundNo) {
      // 如果没有最新编号，生成初始编号
      newInBoundNo = `${getCurrentYearMonth(dateTime)}-0001`;
    } else {
      // 如果有最新编号，将其加1
      const latestNumber = parseInt(latestInBoundNo.split("-")[2]);
      const newNumber = latestNumber + 1;
      newInBoundNo = `${getCurrentYearMonth(dateTime)}-${padNumber(
        newNumber,
        4
      )}`;
    }
    // 在这里使用 newInBoundNo 进行其他操作，或返回给客户端
    return res.cc("获取最新出库单编号成功", true, { newInBoundNo });
  });
};
